﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Srvtools
{
    public partial class RefCommandTextDialog : Form
    {
        public RefCommandTextDialog(string CommandText)
        {
            this.CommandText = CommandText;
            this.OrignCommandText = CommandText;
            InitializeComponent();
        }

        public string CommandText = "";
        private string OrignCommandText = "";
        private String _quotePrefix = "[";
        private String _quoteSuffix = "]";
        //private InfoDataSet ds = new InfoDataSet();
        private void RefCommandTextDialog_Load(object sender, EventArgs e)
        {
            if (CommandText != "")
            {
                DataTable existedTableSchema = GetExistedTableSchema(CommandText);
                string tabName = CommandText.Substring(CommandText.LastIndexOf(' ') + 1);
                listTables.Items.Add(tabName);
                this.txtCommandText.Text = CommandText;
            }
        }

        private void btnAddTable_Click(object sender, EventArgs e)
        {
            List<String> allTablesList = GetAllTablesList();
            CommandTextAddTableDialog addTableDialog = new CommandTextAddTableDialog(allTablesList);
            addTableDialog.ShowDialog();
            List<String> selectedTables = addTableDialog.SelectedTables;
            addTableDialog.Dispose();

            foreach (String i in selectedTables)
            {
                Boolean isAdd = true;
                foreach (Object o in listTables.Items)
                {
                    if (o.ToString() == i)
                    { isAdd = false; break; }
                }

                if (isAdd == false)
                { continue; }
                else
                { listTables.Items.Add(i); }
            }
        }

        private List<String> GetAllTablesList()
        {
            List<String> tablesList = new List<string>();
            String sQL = "select [name] from sysobjects where xtype in('u','U')";
            InfoDataSet ids = new InfoDataSet();
            ids.RemoteName = "GLModule.cmdRefValUse";
            DataSet ds = ids.Execute(sQL);
            int i = ds.Tables[0].Rows.Count;
            for (int j = 0; j < i; j++)
            {
                tablesList.Add(ds.Tables[0].Rows[j][0].ToString());
            }
            ds.Dispose();
            ids.Dispose();
            return tablesList;
        }

        private void listTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listTables.SelectedItem == null)
            { return; }
            String sQL = "select * from " + Quote(listTables.SelectedItem.ToString()) + " where 1 = 0";
            InfoDataSet ids = new InfoDataSet();
            ids.RemoteName = "GLModule.cmdRefValUse";
            DataSet ds = ids.Execute(sQL);
            int i = ds.Tables[0].Columns.Count;
            List<String> columnList = new List<string>();
            for (int j = 0; j < i; j++ )
            {
                columnList.Add(ds.Tables[0].Columns[j].ColumnName);
            }
            ds.Dispose();
            ids.Dispose();
            listColumns.DataSource = columnList;
        }

        private String Quote(String table_or_column)
        {
            if (_quotePrefix == null || _quoteSuffix == null)
                return table_or_column;
            return _quotePrefix + table_or_column + _quoteSuffix;
        }

        private void btnAddAllColumn_Click(object sender, EventArgs e)
        {
            String tableName = "";
            Object table = listTables.SelectedItem;
            if (table == null)
            { throw new Exception("Please choose a table."); }
            else
            { tableName = table.ToString(); }

            String sQL = BuildSQL(this.CommandText, tableName, null, true);
            if (sQL != null || sQL.Length != 0)
            {
                this.CommandText = sQL;
                txtCommandText.Text = this.CommandText;
            }
        }

        private void btnAddColumn_Click(object sender, EventArgs e)
        {
            String tableName = "";
            Object table = listTables.SelectedItem;
            if (table == null)
            { throw new Exception("Please choose a table."); }
            else
            { tableName = table.ToString(); }
            List<String> needAddColumnNamesList = new List<string>();
            foreach (Object item in listColumns.SelectedItems)
            {
                needAddColumnNamesList.Add(tableName + "." + item.ToString());
            }
            if (needAddColumnNamesList.Count == 0)
            { throw new Exception("Please choose columns."); }

            String sQL = BuildSQL(this.CommandText, tableName, needAddColumnNamesList, false);
            if (sQL != null || sQL.Length != 0)
            {
                this.CommandText = sQL;
                txtCommandText.Text = this.CommandText;
            }
        }

        private String BuildSQL(String CommandText, String tableName, List<String> needAddColumnNamesList, Boolean isAdd8)
        {
            String sQL = "";
            if (isAdd8 == true)
            {
                if (CommandText == null || CommandText.Length == 0)
                { return "select " + Quote(tableName) + ".* from " + tableName; }
                else
                { return CommandText; }
            }
            else
            {
                if (CommandText == null || CommandText.Length == 0)
                {
                    foreach (string column in needAddColumnNamesList)
                    {
                        sQL += column.ToString() + ",";
                    }
                    if (sQL != "")
                    {
                        sQL = sQL.Substring(0, sQL.LastIndexOf(','));
                        sQL = "select " + sQL + " from " + tableName;
                    }
                }
                else
                {
                    DataTable existedTableSchema = GetExistedTableSchema(CommandText);
                    List<string> selectedColumnList = GetSelectedColumnsList(tableName, existedTableSchema);
                    List<string> returnColumnList = RemoveRepColumns(selectedColumnList, needAddColumnNamesList);
                    //selectPart
                    String selectPart = GetExistedSelectPart(selectedColumnList);
                    foreach (String c in returnColumnList)
                    {
                        if (selectPart != null && selectPart.Length != 0)
                        { selectPart += ","; }
                        selectPart += c;
                    }
                    sQL = selectPart + " from " + tableName;
                }
                return sQL;
            }
        }

        private DataTable GetExistedTableSchema(string CommandText)
        {
            if (CommandText == null || CommandText == "")
            {
                return null;
            }
            InfoDataSet ids = new InfoDataSet();
            ids.RemoteName = "GLModule.cmdRefValUse";
            ids.Execute(CommandText, true);
            DataTable dt = ids.RealDataSet.Tables["cmdRefValUse"];
            return dt;
        }

        private List<String> GetSelectedColumnsList(string TableName, DataTable schema)
        {
            List<String> selectedColumnList = new List<string>();

            foreach (DataColumn dc in schema.Columns)
            {
                selectedColumnList.Add(TableName + "." + dc.ColumnName);
            }
            return selectedColumnList;
        }

        private List<string> RemoveRepColumns(List<string> selectedColumnList, List<string> needSelectColumnNameList)
        {
            List<string> returnColumnList = new List<string>();

            foreach (string c in needSelectColumnNameList)
            {
                Boolean isNeedAdd = true;
                foreach (string i in selectedColumnList)
                {
                    if (c.ToLower() == i.ToLower())
                    { isNeedAdd = false; break; }
                }

                foreach (string j in returnColumnList)
                {
                    if (c.ToLower() == j.ToLower())
                    { isNeedAdd = false; break; }
                }

                if (isNeedAdd == true)
                { returnColumnList.Add(c); }
            }

            return returnColumnList;
        }

        private String GetExistedSelectPart(List<String> columnsList)
        {
            StringBuilder selectBuilder = new StringBuilder();
            if (columnsList != null && columnsList.Count != 0)
            {
                foreach (String c in columnsList)
                {
                    if (selectBuilder.Length != 0)
                    { selectBuilder.Append(","); }

                    selectBuilder.Append(c);
                }
            }
            return "select " + selectBuilder.ToString();
        }

        private void tabCommandText_SelectedIndexChanged(object sender, EventArgs e)
        {
            InfoDataSet ids = new InfoDataSet();
            if (this.checkShowData.Checked && this.tabCommandText.SelectedIndex == 1)
            {
                ids.RemoteName = "GLModule.cmdRefValUse";
                ids.Execute(this.txtCommandText.Text, true);
                this.dataGridView.DataSource = ids;
                this.dataGridView.DataMember = "cmdRefValUse";
            }
            else
            {
                ids.Dispose();
            }
        }

        private void txtCommandText_TextChanged(object sender, EventArgs e)
        {
            this.CommandText = txtCommandText.Text;
            if (this.CommandText == null || this.CommandText.Length == 0)
            { btnAddAllColumn.Enabled = true; }
            else
            { btnAddAllColumn.Enabled = false; }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.CommandText = OrignCommandText;
            this.Close();
        }
    }
}